<?php 
/* 	These code was generated using phpCIGen v 2.a (21/05/2012)
	#zaqi 		zaqi.smart@gmail.com,http://zenzaqi.blogspot.com,
    #CV. Trust Solution, jl. Saronojiwo 2/19 Surabaya, http://www.ts.co.id

	+ Module  		: t_senddata Model
	+ Description	: For record model process back-end
	+ Filename 		: c_t_senddata.php
 	+ creator 		: 
 	+ Created on 24/Jul/2012 11:40:14

*/

class M_t_senddata extends CI_Model{

		//constructor
		function __construct() {
			parent::__construct();
		}
		
		function adddet_list($kontrak_no,$add_id) {
		$query = "SELECT  t_rapk.div_name FROM t_kontrak
			LEFT JOIN t_spk ON t_spk.spk_id = t_kontrak.t_spk_id
			LEFT JOIN t_komp ON t_spk.t_komp_id = t_komp.komp_id 
			LEFT JOIN t_rapk ON 
			( t_komp.m_branch_id =  t_rapk.m_branch_id AND t_komp.m_job_id =  t_rapk.m_job_id  AND t_komp.t_project_id =  t_rapk.t_project_id )
			LEFT JOIN m_job 
			ON t_komp.m_job_id = m_job.job_id WHERE kontrak_no = '".$kontrak_no."'";
		$row = $this->db->query($query);
		$divisi = $row->row('div_name');
		if ( $divisi != '503. Sub Kontraktor'){
			$sql = "
				SELECT adddet_id as kompdet_id, jobdet_id, jobdet_name, adddet_vol AS kompdet_vol, adddet_unit AS unit_id, unit_name, adddet_price as kompdet_price
				FROM t_add JOIN t_adddet ON(t_adddet.t_add_id = t_add.add_id)
				JOIN t_kontrak ON(t_kontrak.kontrak_id = t_add.t_kontrak_id)
				LEFT JOIN m_jobdet ON(m_jobdet.jobdet_id = t_adddet.m_jobdet_id)
				LEFT JOIN m_unit ON(m_unit.unit_id = t_adddet.adddet_unit)
				WHERE t_kontrak.kontrak_no='".$kontrak_no."'
				AND t_adddet.t_add_id = '". $add_id ."'
				"; 
		}else{
			$sql = "
				SELECT * FROM (
			
				SELECT spkdet_spec, node.kompdet_lft, node.kompdet_id AS kompdet_id, jobdet_id, 
				IFNULL( CONCAT( REPEAT ( '--', COUNT(parent.kompdet_id)- 1 ), jobdet_name ), node.kompdet_uraian )AS jobdet_name, 
				(COUNT(parent.kompdet_id) - 1)AS depth, spkdet_id, adddet_id, 
				IFNULL(a.adddet_vol,node.kompdet_vol) AS kompdet_vol,
				unit_id, unit_name, adddet_price as kompdet_price, 
				IF(a.adddet_vol is null,'-','addendum') AS status  FROM ( 
				SELECT t_kompdet.* FROM t_spkdet JOIN t_kontrak ON( t_kontrak.t_spk_id = t_spkdet.t_spk_id ) 
				JOIN t_kompdet ON( t_kompdet.kompdet_id = t_spkdet.t_kompdet_id ) WHERE t_kontrak.kontrak_no = '".$kontrak_no."' )as node 
				Join( SELECT t_kompdet.* FROM t_spkdet JOIN t_kontrak ON( t_kontrak.t_spk_id = t_spkdet.t_spk_id ) 
				JOIN t_kompdet ON( t_kompdet.kompdet_id = t_spkdet.t_kompdet_id ) WHERE t_kontrak.kontrak_no ='".$kontrak_no."' )as parent 
				ON( node.kompdet_lft BETWEEN parent.kompdet_lft AND parent.kompdet_rgt ) left join m_jobdet 
				ON m_jobdet.jobdet_id = node.m_jobdet_id LEFT JOIN t_spkdet ON( t_spkdet.t_kompdet_id = node.kompdet_id ) 
				LEFT JOIN (select * from t_adddet WHERE t_adddet.t_add_id ='". $add_id ."') as a ON( a.m_jobdet_id = node.m_jobdet_id ) 
				LEFT JOIN m_unit ON( m_unit.unit_id = node.kompdet_unit ) 
				GROUP BY node.kompdet_id ORDER BY node.kompdet_lft 
				
				) t
				UNION ALL 
				SELECT '' as spkdet_spec, 0 as kompdet_lft, 0 as kompdet_id, jobdet_id, 
				jobdet_name, 0 as depth, 0 as spkdet_id, adddet_id, adddet_vol AS volume,
				unit_id, unit_name, adddet_price, 'addendum' AS status
				from t_adddet
				LEFT JOIN t_kompdet
				ON t_adddet.m_jobdet_id = t_kompdet.m_jobdet_id
				left join m_jobdet 
				ON m_jobdet.jobdet_id = t_adddet.m_jobdet_id 
				LEFT JOIN m_unit ON( m_unit.unit_id = t_adddet.adddet_unit ) 
				where kompdet_id is null AND t_adddet.t_add_id ='". $add_id ."'";
			}
		$result = $this->db->query($sql);
		$nbrows = $result->num_rows();
	
		if($nbrows>0){
			foreach($result->result() as $row){
				$arr[] = $row;
			}
			$jsonresult = json_encode($arr);
			return '({"total":"'.$nbrows.'","results":'.$jsonresult.'})';
		} else {
			return '({"total":"0", "results":""})';
		}
	}
		
		function t_senddata_list($filter,$start,$end){
			$sql = "
				SELECT
					t_kontrak.kontrak_id AS data_id,
					m_branch.branch_id,
					m_branch.branch_name,
					t_project.project_id,
					t_project.project_code,
					t_project.project_name,
					m_job.job_id,
					m_job.job_name,
					t_kontrak.kontrak_date,
					t_kontrak.kontrak_no,
					t_kontrak.kontrak_begin,
					t_kontrak.kontrak_end,
					m_vendor.vendor_name,
					m_vendor.vendor_id,
					m_vendor.vendor_address,
					m_vendor.vendor_cp,
					'Kontrak' AS data_type
				FROM t_kontrak
				JOIN t_spk ON t_kontrak.t_spk_id = t_spk.spk_id
				JOIN t_nego ON t_nego.nego_id = t_spk.t_nego_id
				JOIN t_komp ON t_komp.komp_id = t_spk.t_komp_id
				JOIN m_branch ON t_komp.m_branch_id = m_branch.branch_id
				JOIN t_project ON t_komp.t_project_id = t_project.project_id
				JOIN m_job ON t_komp.m_job_id = m_job.job_id
				JOIN m_vendor ON t_komp.m_vendor_id = m_vendor.vendor_id
				WHERE t_kontrak.kontrak_havesend = 'n' 
				";
			if ($filter<>""){
				$sql .=preg_match("/WHERE/i",$sql)? " AND ":" WHERE ";
				$sql .= " (project_code LIKE '%".addslashes($filter)."%' or project_name LIKE '%" . addslashes($filter) . "%' or vendor_name LIKE '%" . addslashes($filter) . "%' 
					or kontrak_no LIKE '%" . addslashes($filter) . "%' or job_name LIKE '%" . addslashes($filter) . "%'
				)";
			}
			$sql .= "
				 
				UNION
				
				SELECT
				t_add.add_id AS data_id,
				m_branch.branch_id,
				m_branch.branch_name,
				t_project.project_id,
				t_project.project_code,
				t_project.project_name,
				m_job.job_id,
				m_job.job_name,
				t_kontrak.kontrak_date,
				t_kontrak.kontrak_no,
				t_kontrak.kontrak_begin,
				t_kontrak.kontrak_end,
				m_vendor.vendor_name,
				m_vendor.vendor_id,
				m_vendor.vendor_address,
				m_vendor.vendor_cp,
				'Adendum' AS data_type
				FROM t_add
				JOIN t_kontrak ON t_add.t_kontrak_id = t_kontrak.kontrak_id
				JOIN t_spk ON t_kontrak.t_spk_id = t_spk.spk_id
				JOIN t_nego ON t_nego.nego_id = t_spk.t_nego_id
				JOIN t_komp ON t_komp.komp_id = t_spk.t_komp_id
				JOIN m_branch ON t_komp.m_branch_id = m_branch.branch_id
				JOIN t_project ON t_komp.t_project_id = t_project.project_id
				JOIN m_job ON t_komp.m_job_id = m_job.job_id
				JOIN m_vendor ON t_komp.m_vendor_id = m_vendor.vendor_id
				WHERE t_add.add_havesend = 'n'
			";
			if ($filter<>""){
				$sql .=preg_match("/WHERE/i",$sql)? " AND ":" WHERE ";
				$sql .= " (project_code LIKE '%".addslashes($filter)."%' or project_name LIKE '%" . addslashes($filter) . "%' or vendor_name LIKE '%" . addslashes($filter) . "%' 
					or kontrak_no LIKE '%" . addslashes($filter) . "%' or job_name LIKE '%" . addslashes($filter) . "%'
				)";
			}
			$result = $this->db->query($sql);
			$nbrows = $result->num_rows();
			$limit = $sql." LIMIT ".$start.", ".$end;
			$result = $this->db->query($limit);
			
			if($nbrows > 0){
				foreach($result->result() as $row){
					$arr[] = $row;
				}
				$jsonresult = json_encode($arr);
				return '({"total":"'.$nbrows.'","results":'.$jsonresult.'})';
			}else{
				return '({"total":"0", "results":""})';
			}
		}
		function getdata_doctrac_mproc(){
			$this->load->database('doctrac', TRUE);
			$sql = "SELECT proc_id, proc_name FROM m_proc";
			$result = $this->db->query($sql);
			$nbrows = $result->num_rows();

			if($nbrows>0){
				foreach($result->result() as $row){
					$arr[] = $row;
				}
				$jsonresult = json_encode($arr);
				return '({"total":"'.$nbrows.'","results":'.$jsonresult.'})';
			} else {
				return '({"total":"0", "results":""})';
			}
		}
		function get_dataadendum($add_id){
			$sql = "SELECT * FROM t_add WHERE add_id = '" . $add_id . "'";
			$result = $this->db->query($sql);
			$nbrows = $result->num_rows();

			if($nbrows>0){
				foreach($result->result() as $row){
					$arr[] = $row;
				}
				$jsonresult = json_encode($arr);
				return '({"total":"'.$nbrows.'","results":'.$jsonresult.'})';
			} else {
				return '({"total":"0", "results":""})';
			}
		}

		function get_dataforsend($kontrak_id, $jenis_data){
			if($jenis_data == 'Adendum'){
				$kuery = "
					SELECT
						t_kontrak.kontrak_id,
						m_branch.branch_id,
						t_project.project_code,
						t_project.project_name,
						m_job.job_name,
						t_project.project_value,
						m_vendor.vendor_name,
						t_kontrak.kontrak_no,
						t_kontrak.kontrak_date,
						t_kontrak.kontrak_begin,
						t_kontrak.kontrak_end,
						t_nego.nego_bayar,
						t_nego.nego_rapktotal,
						t_rapk.m_div_id,
						t_rapk.rapk_procurement
					FROM
						t_add
					JOIN t_kontrak on t_add.t_kontrak_id = t_kontrak.kontrak_id
					JOIN t_spk ON t_kontrak.t_spk_id = t_spk.spk_id
					JOIN t_nego ON t_nego.nego_id = t_spk.t_nego_id
					JOIN t_komp ON t_komp.komp_id = t_spk.t_komp_id
					JOIN m_branch ON t_komp.m_branch_id = m_branch.branch_id
					JOIN t_project ON t_komp.t_project_id = t_project.project_id
					JOIN m_job ON t_komp.m_job_id = m_job.job_id
					JOIN m_vendor ON t_komp.m_vendor_id = m_vendor.vendor_id
					JOIN t_rapk ON t_rapk.t_project_id = t_project.project_id
					WHERE
						t_add.add_id = '" . $kontrak_id . "'
				";
			}else{
				$kuery = "
					SELECT
						t_kontrak.kontrak_id,
						m_branch.branch_id,
						t_project.project_code,
						t_project.project_name,
						m_job.job_name,
						t_project.project_value,
						m_vendor.vendor_name,
						t_kontrak.kontrak_no,
						t_kontrak.kontrak_date,
						t_kontrak.kontrak_begin,
						t_kontrak.kontrak_end,
						t_nego.nego_bayar,
						t_nego.nego_rapktotal,
						t_rapk.m_div_id,
						t_rapk.rapk_procurement
					FROM
						t_kontrak
					JOIN t_spk ON t_kontrak.t_spk_id = t_spk.spk_id
					JOIN t_nego ON t_nego.nego_id = t_spk.t_nego_id
					JOIN t_komp ON t_komp.komp_id = t_spk.t_komp_id
					JOIN m_branch ON t_komp.m_branch_id = m_branch.branch_id
					JOIN t_project ON t_komp.t_project_id = t_project.project_id
					JOIN m_job ON t_komp.m_job_id = m_job.job_id
					JOIN m_vendor ON t_komp.m_vendor_id = m_vendor.vendor_id
					JOIN t_rapk ON t_rapk.t_project_id = t_project.project_id
					WHERE
						t_kontrak.kontrak_id = '" . $kontrak_id . "'
				";
			}
			$query = $this->db->query($kuery);
			$result = $query->result();
			return $result;
		}
		// check proyek di database doctrac
		function doctrac_checkproject($project_kode){
			$this->load->database('doctrac', true);
			$kuery = "select projectlist_id from t_projectlist where projectlist_code = '" . $project_kode . "' LIMIT 0,1";
			$query = $this->db->query($kuery);
			$num_rows = $query->num_rows();
			if($num_rows > 0 ){
				$row = $query->row();
				$projectlist_id = $row->projectlist_id;
				return $projectlist_id;
			}else{
				return 0;
			}
		}
		// tambah proyek di database doctrac
		function create_doctracprojectlist($project_kode, $project_name, $branch_id){
			$this->load->database('doctrac', true);
			$data = array(
				'projectlist_code' => $project_kode,
				'projectlist_name' => $project_name,
				'm_branch_branch_id' => $branch_id
			);
			$this->db->insert('t_projectlist', $data);
			if($this->db->affected_rows()){
				return $this->db->insert_id();
			}else{
				return 0;
			}
		}
		// check company di database doctrac
		function doctrac_checkcompany($vendor_name){
			$this->load->database('doctrac', true);
			$kuery = "select company_id from m_company where LOWER(company_name) = '" . strtolower($vendor_name) . "' LIMIT 0,1";
			$query = $this->db->query($kuery);
			$num_rows = $query->num_rows();
			if($num_rows > 0 ){
				$row = $query->row();
				$company_id = $row->company_id;
				return $company_id;
			}else{
				return 0;
			}
		}
		// tambah company di database doctrac
		function create_doctraccompany($vendor_name){
			$this->load->database('doctrac', true);
			$data = array(
				'company_name' => $vendor_name,
				'company_active' => 'y',
				'm_subdiv_subdiv_id' => '1',
				'm_subdiv_m_div_div_id' => '1'
			);
			$this->db->insert('m_company', $data);
			if($this->db->affected_rows()){
				return $this->db->insert_id();
			}else{
				return 0;
			}
		}
		function doctrac_getworkflowid($filter_criteria,$sort){
			$this->load->database('doctrac', true);
			$kuery = "select m_workflow_workflow_id from m_criteria where " . $filter_criteria . " ORDER BY " . $sort . " LIMIT 0,1";
			$query = $this->db->query($kuery);
			$num_rows = $query->num_rows();
			if($num_rows > 0 ){
				$row = $query->row();
				$m_workflow_workflow_id = $row->m_workflow_workflow_id;
				return $m_workflow_workflow_id;
			}else{
				return 0;
			}
		}
		function doctrac_getdetworkflow($filter_criteria,$sort){
			$this->load->database('doctrac', true);
			$kuery = "select * from m_detworkflow where " . $filter_criteria . " ORDER BY " . $sort;
			$query = $this->db->query($kuery);
			$num_rows = $query->num_rows();
			if($num_rows > 0 ){
				$result = $query->first_row();
				return $result;
			}else{
				return 0;
			}
		}
		function read_vchecklist($filter_criteria){
			$this->load->database('doctrac', true);
			$kuery = "select * from v_checklist where " . $filter_criteria;
			$query = $this->db->query($kuery);
			$num_rows = $query->num_rows();
			if($num_rows > 0 ){
				$result = $query->result();
				return $result;
			}else{
				return 0;
			}
		}
		function create_projdoc($array_data){
			$this->db->insert('t_projdoc', $array_data);
			if($this->db->affected_rows()){
				return $this->db->insert_id();
			}else{
				return 0;
			}
		}
		function create_tdocflow($array_data){
			$this->db->insert('t_docflow', $array_data);
			if($this->db->affected_rows()){
				return $this->db->insert_id();
			}else{
				return 0;
			}
		}
		function create_tchecklistflow($array_data){
			$this->db->insert('t_checklistflow', $array_data);
			if($this->db->affected_rows()){
				return $this->db->insert_id();
			}else{
				return 0;
			}
		}
		function tdocflow_lastrecord(){
			$this->db->order_by('docflow_id','asc');
			$this->db->from('t_docflow');
			$query =$this->db->get();
			return $query->last_row();
		}
	function update_status($kontrak_id,$jenis_data){
		$this->load->database('default', true);
		if($jenis_data == 'Adendum'){
			$kuery = "Update t_add set add_havesend = 'y' where add_id = '" . $kontrak_id . "'";
			$query = $this->db->query($kuery);
		}else{
			$kuery = "Update t_kontrak set kontrak_havesend = 'y' where kontrak_id = '" . $kontrak_id . "'";
			$query = $this->db->query($kuery);
		}
		if($this->db->affected_rows()){
			return 1;
		}else{
			return 0;
		}
	}
}
?>